package com.unitechs.trap.excel.webapi.service.impl.wrangling;

import com.unitechs.trap.common.pojo.trap.entity.AllData;
import com.unitechs.trap.excel.service.IWranglingService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
@Slf4j
public class WranglingServiceImpl implements IWranglingService {
    private static String outcome;

    @Override
    public void wranglingDataFromExcel(String url, String oldUrl) {
        List<AllData> list = new ArrayList<>();
        AllData lastData = new AllData();
        try {
            File fileOld = new File(oldUrl);
            File[] files = fileOld.listFiles();
            for (File f : files) {
                if (!f.isDirectory() && f.getName().endsWith(".xls")) {
                    //创建工作簿对象
                    HSSFWorkbook xssfWorkbook = new HSSFWorkbook(new FileInputStream(f.getPath()));
                    //读取第1个工作表
                    HSSFSheet sheet = xssfWorkbook.getSheetAt(0);
                    //获取最后一行的num，即总行数。此处从0开始
                    int maxRow = sheet.getLastRowNum();
                    for (int row = 1; row <= maxRow; row++) {
                        AllData allData = new AllData();
                        allData.setTrapOid(lastData, sheet, row, "A");
                        allData.setTrapTypeCode(lastData, sheet, row, "A");
                        allData.setAlarmlevelString(lastData, sheet, row, "A");
                        allData.setAlarmclassCodeString(lastData, sheet, row, "A");
                        allData.setTrapTypeDesc(allData, lastData, sheet, row, "A");
                        allData.setTrapTypeCodeId(lastData, sheet, row, "A");
                        lastData = allData;
                        list.add(allData);
                    }
                }
            }

            FileOutputStream fileOutputStream = new FileOutputStream(url);
            HSSFWorkbook xssfWorkbook = new HSSFWorkbook();
            HSSFSheet sheet = xssfWorkbook.createSheet();
            int row = 0;

            for (AllData allData : list) {
                HSSFRow hssfRow = sheet.createRow(row++);
                hssfRow.createCell(0).setCellValue(allData.getTrapOid());
                hssfRow.createCell(1).setCellValue(allData.getTrapTypeCode());
                hssfRow.createCell(2).setCellValue(allData.getAlarmlevelString());
                hssfRow.createCell(3).setCellValue(allData.getAlarmclassCode());
                hssfRow.createCell(4).setCellValue(allData.getTrapTypeDesc());
                hssfRow.createCell(5).setCellValue(allData.getTrapTypeCodeId());
            }
            xssfWorkbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();


        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void wranglingDataToExcel(String urlFrom, String urlTo) {
        File dirTo = new File(urlTo);
        File dirFrom = new File(urlFrom);
        readDirTo(dirTo, dirFrom);
    }

    private void readDirTo(File dirTo, File dirFrom) {
        if (dirTo.isDirectory()) {
            File[] subs = dirTo.listFiles();
            for (File sub : subs) {
                readDirTo(sub, dirFrom);
            }
            return;
        } else if (dirTo.getName().endsWith(".xls")) {
            String lastTrapOid = "";
            String trapOid = "";
            try {
                HSSFWorkbook xssfWorkbook = new HSSFWorkbook(new FileInputStream(dirTo.getPath()));
                HSSFSheet sheet = xssfWorkbook.getSheetAt(0);
                int maxRow = sheet.getLastRowNum();
                for (int row = 1; row <= maxRow; row++) {
                    if (sheet.getRow(row).getCell(1) == null || sheet.getRow(row).getCell(1).toString().equals("")) {
                        trapOid = lastTrapOid;
                    } else {
                        trapOid = sheet.getRow(row).getCell(1).toString();
                    }
                    outcome = "";
                    readDirHaveJson(dirTo, dirFrom, trapOid, row);
                    log.info("文件名:{};行数:{};outcome:{}", dirTo.getName(), row, outcome);
                    if (outcome.equals("")) {
                        continue;
                    }
                    HSSFRow hssfRow = sheet.getRow(row);
                    if (hssfRow == null) {
                        hssfRow = sheet.createRow(row);
                    }
                    HSSFCell hssfCell = hssfRow.getCell(8);
                    if (hssfCell == null) {
                        hssfCell = hssfRow.createCell(8);
                    }
                    hssfCell.setCellValue(outcome);
                    FileOutputStream fos = new FileOutputStream(dirTo);
                    xssfWorkbook.write(fos);
                    fos.flush();
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 读取含报文和Json数据的文件路径路径
     * @param dirTo
     * @param dirFrom
     * @param trapOidTo
     * @param rowTo
     */
    private void readDirHaveJson(File dirTo, File dirFrom, String trapOidTo, Integer rowTo) {
        if (dirFrom.isDirectory()) {
            File[] subs = dirFrom.listFiles();
            for (File sub : subs) {
                if (!outcome.equals("")) {
                    return;
                }
                readDirHaveJson(dirTo, sub, trapOidTo, rowTo);
            }
            return;
        } else if (dirFrom.getName().endsWith(".xls")) {
            String lastTrapOid = "";
            String trapOid = "";
            try {
                HSSFWorkbook xssfWorkbook = new HSSFWorkbook(new FileInputStream(dirFrom.getPath()));
                HSSFSheet sheet = xssfWorkbook.getSheetAt(0);
                int maxRow = sheet.getLastRowNum();
                for (int row = 1; row <= maxRow; row++) {
                    if (sheet.getRow(row).getCell(1) == null || sheet.getRow(row).getCell(1).toString().equals("")) {
                        trapOid = lastTrapOid;
                    } else {
                        trapOid = sheet.getRow(row).getCell(1).toString();
                    }
                    if (trapOid.equals(trapOidTo)) {
                        outcome = sheet.getRow(row).getCell(5).toString();
                        log.info("递归中的outcome{}", outcome);
                        HSSFRow hssfRow = sheet.getRow(row);
                        if (hssfRow == null) {
                            hssfRow = sheet.createRow(row);
                        }
                        HSSFCell hssfCell = hssfRow.getCell(6);
                        if (hssfCell == null) {
                            hssfCell = hssfRow.createCell(6);
                        }
                        hssfCell.setCellValue("匹配到对应的TrapOid\n文件名称:" + dirTo.getName() + "\n数据位置:" + rowTo + "行");
                        FileOutputStream fos = new FileOutputStream(dirFrom);
                        xssfWorkbook.write(fos);
                        fos.flush();
                        fos.close();
                        break;
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 从报文中获取TrapOid
     * @param url
     */
    @Override
    public void getTrapOidByMessage(String url) {
        try {
            File file = new File(url);
            File[] files = file.listFiles();
            for (File f : files) {
                if (!f.isDirectory() && f.getName().endsWith(".xls")) {
                    //创建工作簿对象
                    HSSFWorkbook xssfWorkbook = new HSSFWorkbook(new FileInputStream(f.getPath()));
                    //读取第1个工作表
                    HSSFSheet sheet = xssfWorkbook.getSheetAt(0);

                    int maxRow = sheet.getLastRowNum();
                    for (int row = 1; row <= maxRow; row++) {
                        String message = "";
                        if (sheet.getRow(row).getCell(1) == null
                                || sheet.getRow(row).getCell(1).toString().equals("")
                        ) {
                            continue;
                        }

                        String regexAlarm = "1\\.3\\.6\\.1\\.6\\.3\\.1\\.1\\.4\\.1\\.0\\s*=\\s*(?<value>.*?)\\s*;";
                        Pattern pattern = Pattern.compile(regexAlarm);
                        Matcher matcher = pattern.matcher(sheet.getRow(row).getCell(1).toString());
                        while (matcher.find()) {
                            message = matcher.group("value");
                        }
                        HSSFRow hssfRow = sheet.getRow(row);
                        if (hssfRow == null) {
                            hssfRow = sheet.createRow(row);
                        }
                        // 获取现有的单元格对象，如果不存在则创建新单元格
                        HSSFCell hssfCell = hssfRow.getCell(2);
                        if (hssfCell == null) {
                            hssfCell = hssfRow.createCell(2);
                        }
                        hssfCell.setCellValue(message);
                    }
                    FileOutputStream fos = new FileOutputStream(f);
                    xssfWorkbook.write(fos);
                    fos.flush();
                    fos.close();
                }

            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
